﻿using jinmaMesProxy.DataAccess;
using jinmaMesProxy.Excep;
using jinmaMesProxy.Model;
using System.Diagnostics.Eventing.Reader;
using System.Text;

namespace jinmaMesProxy.Service
{
    public class StockService
    {
        ErpDbInstance dbInstance;
        public StockService()
        {
            dbInstance = ServiceLoader.GetService<ErpDbInstance>();
        }

        public void SetCurrentStock(string material, string[] free,string warehouse,decimal qty,bool approved)
        {
            bool setDirectly = approved || !SimpleSqlHelper.CalQtyWhenApproved();
            var idx = IdxItem(material, free);
            InsertBlankStock(idx,material,warehouse,free);
            UpdateStock(idx, warehouse, qty, setDirectly);
        }
        private int IdxItem(string material, string[] free)
        {
            StringBuilder sb = new StringBuilder( "select id from SCM_Item where 1=1 and cinvcode = @p0");
            List<KeyValuePair<string, object>> p = new List<KeyValuePair<string, object>>();
            p.Add(new KeyValuePair<string, object>("@p0",material));

            for (int i = 0; i < free.Length; i++)
            {
                if (!String.IsNullOrWhiteSpace(free[i]))
                {
                    sb.AppendFormat(" and cfree{0} = @p{0}", (i + 1));
                    p.Add(new KeyValuePair<string, object>($"@p{i + 1}", free[i]));
                }
            }
            using (var cmd = dbInstance.MakeQueryCommand(sb.ToString(), p.ToArray()))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return reader.GetInt32(0);
                    }
                }
            }
            //找不到的话就Insert
            var p2 = new List<KeyValuePair<string, object>>();
            p2.Add(new KeyValuePair<string, object>("@p0", material));
            for (int i = 0; i < free.Length; i++)
            {
                p2.Add(new KeyValuePair<string, object>($"@p{i + 1}", free[i]));
            }
            var insstr = "insert into scm_item(cinvcode,cfree1,cfree2,cfree3,cfree4,cfree5," +
                "cfree6,cfree7,cfree8,cfree9,cfree10,partid) values(@p0,@p1,@p2,@p3,@p4,@p5,"+
                "@p6,@p7,@p8,@p9,@p10,0); SELECT SCOPE_IDENTITY();";
            using (var cmd = dbInstance.MakeQueryCommand(insstr, p2.ToArray()))
            {
                var id = Convert.ToInt32(cmd.ExecuteScalar());
                return id;
            }
        }
        private void InsertBlankStock(int itemid, string material, string wh, string[] free)
        {
            var touch = "select count(1) from CurrentStock where itemid = @itemid and cwhcode = @wh";
            var ins = "insert into CurrentStock(cwhcode,cinvcode,itemid,isotype,isodid,iquantity,foutquantity,finquantity," +
                "cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10) " +
                "values(@wh,@material,@itemid,0,'',0,0,0," +
                "@cfree1,@cfree2,@cfree3,@cfree4,@cfree5,@cfree6,@cfree7,@cfree8,@cfree9,@cfree10)";
            using (var cmd = dbInstance.MakeQueryCommand(touch,
                new KeyValuePair<string, object?>("@itemid", itemid),
                new KeyValuePair<string, object?>("@wh", wh)))
            {
                var i = (int)cmd.ExecuteScalar();
                if (i > 0) return;
            }
            using (var cmd = dbInstance.MakeQueryCommand(touch,
                new KeyValuePair<string, object?>("@itemid", itemid),
                new KeyValuePair<string, object?>("@wh", wh)))
            {
                var i = (int)cmd.ExecuteScalar();
                if (i > 0) return;
            }
            var p = new List<KeyValuePair<string, object>>();
            p.Add(new KeyValuePair<string, object>("@itemid", itemid));
            p.Add(new KeyValuePair<string, object>("@wh", wh));
            p.Add(new KeyValuePair<string, object>("@material", material));
            for (int i = 0; i < free.Length; i++)
            {
                p.Add(new KeyValuePair<string, object>($"@cfree{i + 1}", free[i]));
            }
            using (var cmd = dbInstance.MakeQueryCommand(ins,p.ToArray()))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public List<StockInfo> GetQty(IEnumerable<string> materials)
        {
            var _materials = materials.Where(r => !String.IsNullOrWhiteSpace(r)).Distinct().ToArray();
            if (_materials.Count() > 1000)
                throw new MyException("单次最多查询100个物料");
            StringBuilder sb = new StringBuilder("(");
            List<KeyValuePair<string, object>> keyValuePairs = new List<KeyValuePair<string, object>>();
            for (int i = 0; i < _materials.Length; i++)
            {
                sb.Append($"@p{i},");
                keyValuePairs.Add(new KeyValuePair<string, object>($"@p{i}", _materials[i]));
            }
            sb[sb.Length - 1] = ')';
            ErpDocQuery<StockInfo> query = new ErpDocQuery<StockInfo>();
            var result = query.Query("and iquantity !=0 and cinvcode in " + sb.ToString(), keyValuePairs.ToArray());
            return result;
        }

        private void UpdateStock(int itemid,string wh, decimal qty,bool setDirectly)
        {
            if (qty < 0 && setDirectly)
            {
                var queryStock = "select top 1 iQuantity,cinvcode from CurrentStock where itemid = @itemid and cwhcode = @wh";
                using (var cmd = dbInstance.MakeQueryCommand(queryStock,
                    new KeyValuePair<string, object?>("@itemid", itemid),
                    new KeyValuePair<string, object?>("@wh", wh)))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        { 
                            var stock = reader.GetDecimal(0);
                            var inv = reader.GetString(1);
                            if (stock < Math.Abs(qty))
                                throw new MyException($"{inv}库存数量不足");
                        }
                    }
                }
            }

            var sql = "";
            if (setDirectly)
            {
                sql = "update CurrentStock set iQuantity = iQuantity + @qty where itemid=@itemid and cwhcode = @wh";
            }
            else if (qty > 0)
            {
                sql = "update CurrentStock set fInQuantity = fInQuantity + @qty where itemid=@itemid and cwhcode = @wh";
            }
            else
            {
                //注意此时是负值，需要负负得正来更新fOutQuantity
                sql = "update CurrentStock set fOutQuantity = fOutQuantity - @qty where itemid=@itemid and cwhcode = @wh";
            }
            using (var cmd = dbInstance.MakeQueryCommand(sql, 
                new KeyValuePair<string, object?>("@itemid", itemid),
                new KeyValuePair<string, object?>("@wh", wh),
                new KeyValuePair<string, object?>("@qty", qty)))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }
}
